-- @owner: lihongji
-- @date: 2022-07-14
-- @testpoint: to_timestamp,WHERE exists/NOT exists的测试

--step1:建表;expect:成功
drop table if exists t_ustore_to_timestamp_case0022;
create table t_ustore_to_timestamp_case0022( col_1 bigint, col_2 timestamp with  time zone,
col_3 bool,col_4 decimal,col_5 text,col_6 smallint,col_7 char(30),col_8 double precision,col_9 text,col_10 character varying(30),
col_11 bool ,col_12 bytea ,col_13 real ,col_14 numeric ,col_15 blob ,col_16 integer ,col_17 int ,col_18 timestamp with  time zone ,
col_19 binary_integer ,col_20 interval day to second ,col_21 boolean, col_22 nchar(30),col_23 binary_integer,col_24 nchar(100),col_25 character(1000), col_26 text,
col_27 float,col_28 float8,col_29 bigint,col_30 timestamp with  time zone ,col_31 timestamp,col_32 blob,col_33 interval year to month,
col_34 character(30),col_35 smallint,col_36 blob,col_37 char(300),col_38 float,col_39 raw(100),col_40 clob ,col_41 binary_double,col_42 number(6,2),
col_43 decimal(6,2),col_44 varchar2(50),col_45 varchar(30),col_46 nvarchar2(100),col_47 numeric(12,6),col_48 nvarchar2(30),col_49 date,
col_50 blob ,col_51 integer,col_52 binary_double,col_53 decimal(12,6),col_54 raw(8000),col_55 clob,col_56 varchar2(8000),col_57 smalldatetime,
col_58 number(12,6),col_59 nvarchar2(4000),col_60 bytea ,col_61 blob,col_62 smalldatetime,col_63 blob,col_64 varchar(1000),col_65 date,
constraint to_timestamp primary key(col_1)
) with (storage_type=ustore);
--step2:创建序列;expect:成功
drop sequence if exists to_timestamp_seq;
create sequence to_timestamp_seq increment by 1 start with 100000;
--step3:清空数据;expect:成功
truncate table t_ustore_to_timestamp_case0022;
--step4：插入数据;expect:成功
begin
 for i in 1..1000 loop
   insert into t_ustore_to_timestamp_case0022 values(to_timestamp_seq.nextval,to_timestamp('12-sep-2014'),
   true,3.1415926+to_timestamp_seq.nextval,lpad('abc','5000','a123&^%djgk'),
   i,lpad('abc','30','1'),i+1.456789445455,lpad('abc','5000','a123&^%djgk'),
   lpad('abc','30','b'),false,lpad('10','5000','0101')::bytea,3.1415926+to_timestamp_seq.nextval,
   i/4,lpad('10','8000','01010')::blob,i,i,to_timestamp('12-sep-2014'),'1',
   (interval '4 5:12:10.222' day to second(3)),0,rpad('abc','30','e'),to_timestamp_seq.nextval,
   rpad('abc','100','exc'),lpad('abc','1000','a123&^%djgk'),lpad('abc','5000','a123&^%djgk'),
   i/4,to_timestamp_seq.nextval-99,i*3.1415,to_timestamp('12-sep-2014'),
   to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
   lpad('abc','5000','a123')::blob,(interval '12' year),rpad('abc','30','&'),
   i,lpad('10',5000,'01010')::blob,rpad('abc','300','exc'),i/2.15,rpad('0F',100,'AADB9')::raw,
   lpad('abc','5000','a123%djgk'),1.0E+100,3.14+i,i+445.255,rpad('abc','30','&'),lpad('abc','30','&'),
   rpad('abc','100','&GDsh'),125563.141592,rpad('abc','30','e'),to_timestamp('12-sep-2014'),
   lpad('abc','5000','123')::blob,to_timestamp_seq.nextval+2,-1.79E+100,98*0.99,
   lpad('10','8000','01010')::raw,lpad('abc','5000','a123&^%djgk'),rpad('abc','8000','a123&^%djgk'),
   to_timestamp('12-sep-2014'),25563.1415,lpad('abc','3000','a字符串123&^%djgk'),lpad('10',2000,'01010')::bytea,
   lpad('10','200','01010')::blob,to_timestamp('12-sep-2014'),'010101111111100000000000000',
   rpad('abc','1000','&GDsh'),to_timestamp('12-Sep-10 12:10:10.123000','DD-Mon-YY HH24:MI:SS.FF'));
   end loop;
end;
/
--step5:WHERE exists/NOT exists的测试；expect：成功
select to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd'),to_timestamp(to_char(col_49,'yyyy-mm-dd'),'yyyy-mm-dd') from t_ustore_to_timestamp_case0022
where exists (select to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd') from t_ustore_to_timestamp_case0022) group by to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd'),to_timestamp(to_char(col_49,'yyyy-mm-dd'),'yyyy-mm-dd')
having  extract(year from to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd'))>'2018' and extract(year from to_timestamp(to_char(col_49,'yyyy-mm-dd'),'yyyy-mm-dd'))<'2021'
order by to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd'),to_timestamp(to_char(col_49,'yyyy-mm-dd'),'yyyy-mm-dd');

select  to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd'),to_timestamp(to_char(col_49,'yyyy-mm-dd'),'yyyy-mm-dd') from t_ustore_to_timestamp_case0022
where  exists (select to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd') from t_ustore_to_timestamp_case0022 where extract(year from to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd'))>'2019')
group by to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd'),to_timestamp(to_char(col_49,'yyyy-mm-dd'),'yyyy-mm-dd')
having  extract(year from to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd'))>'2018' and extract(year from to_timestamp(to_char(col_49,'yyyy-mm-dd'),'yyyy-mm-dd'))<'2021'
order by to_timestamp(to_char(col_65,'yyyy-mm-dd'),'yyyy-mm-dd'),to_timestamp(to_char(col_49,'yyyy-mm-dd'),'yyyy-mm-dd');
--step6:清理环境;expect:成功
drop table if exists t_ustore_to_timestamp_case0022;
